﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Business
{
    public partial class frm_Add_ImportQuotation : Form
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
        
        public frm_Add_ImportQuotation()
        {
            InitializeComponent();

            mySetting = new INISettings();
            myConnection = new SqlConnection(mySetting.SQLConnectionString);

            this.inputDate.EditValue = DateTime.Now.Date;
            this.qty1.Text = "9999999";
            LoadPart();
            LoadCurrency();
        }

        ///////////////////////////////
        /// Load Part List
        //////////////////////////////
        private void LoadPart()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT PartNo FROM [222_Part] ORDER BY PartNo";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text = dataReader["PartNo"].ToString();
                    item.Value = dataReader["PartNo"].ToString();
                    this.partNo.Properties.Items.Add(item);
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get Part list");
            }
        }        

        ///////////////////////////////
        /// Load Currency List
        //////////////////////////////
        private void LoadCurrency()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT CurrencyID, CurrencyName FROM [222_Currency] ORDER BY CurrencyName";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text = dataReader["CurrencyName"].ToString();
                    item.Value = dataReader["CurrencyID"].ToString();
                    this.currencyID.Properties.Items.Add(item);
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get Currency list");
            }
        }

        private void partNo_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.partNo.SelectedIndex >= 0)
            {
                ComboboxItem item = (ComboboxItem)this.partNo.SelectedItem;
                try
                {
                    this.supplierID.Properties.Items.Clear();
                    myConnection.Open();
                    SqlCommand cmd_sql = myConnection.CreateCommand();
                    cmd_sql.CommandText = "SELECT p.[SupplierID], s1.[SupplierName] AS Supplier1,"
                        + " p.[SecondSupplierID], s2.[SupplierName] AS Supplier2, m.EnglishName, u.UnitNameEN"
                        + " FROM [222_Part] AS p LEFT JOIN"
                        + " [222_Supplier] AS s1 ON p.[SupplierID]=s1.[SupplierID] LEFT JOIN"
                        + " [222_Supplier] AS s2 ON p.[SecondSupplierID]=s2.[SupplierID] LEFT JOIN"
                        + " [222_Maker] AS m ON p.[MakerID]=m.[MakerID] LEFT JOIN"
                        + " [222_Unit] AS u ON p.[BuyingUnit]=u.[UnitID]"
                        + " WHERE p.[PartNo]='" + item.Value.ToString() + "'";
                    SqlDataReader dataReader = cmd_sql.ExecuteReader();
                    if (dataReader.Read())
                    {
                        if (dataReader["SupplierID"] != DBNull.Value)
                        {
                            ComboboxItem item2 = new ComboboxItem();
                            item2.Text = dataReader["Supplier1"].ToString();
                            item2.Value = dataReader["SupplierID"].ToString();
                            this.supplierID.Properties.Items.Add(item2);
                            this.supplierID.SelectedIndex = 0;
                        }

                        if (dataReader["SecondSupplierID"] != DBNull.Value)
                        {
                            ComboboxItem item2 = new ComboboxItem();
                            item2.Text = dataReader["Supplier2"].ToString();
                            item2.Value = dataReader["SecondSupplierID"].ToString();
                            this.supplierID.Properties.Items.Add(item2);
                        }

                        this.makerID.Text = dataReader["EnglishName"].ToString();
                        this.unitID.Text = dataReader["UnitNameEN"].ToString();
                    }
                    dataReader.Close();
                    cmd_sql.Dispose();
                    myConnection.Close();
                }
                catch //(Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show("Cannot get detail for selected Part");
                    //MessageBox.Show(ex.Message);
                }
            }
        }

        private void qtyBasePrice_CheckedChanged(object sender, EventArgs e)
        {
            if (qtyBasePrice.Checked)
            {
                qty1.Enabled = true;
                qty1.Text = null;
                qty2.Enabled = true;
                qty3.Enabled = true;
                qty4.Enabled = true;
                qty5.Enabled = true;
                qty6.Enabled = true;
                qty7.Enabled = true;
                qty8.Enabled = true;
                qty9.Enabled = true;
                qty10.Enabled = true;

                price1.Enabled = true;
                price2.Enabled = true;
                price3.Enabled = true;
                price4.Enabled = true;
                price5.Enabled = true;
                price6.Enabled = true;
                price7.Enabled = true;
                price8.Enabled = true;
                price9.Enabled = true;
                price10.Enabled = true;
            }
            else
            {
                qty1.Enabled = false;
                qty1.Text = "9999999";
                qty2.Enabled = false;
                qty2.Text = null;
                qty3.Enabled = false;
                qty3.Text = null;
                qty4.Enabled = false;
                qty4.Text = null;
                qty5.Enabled = false;
                qty5.Text = null;
                qty6.Enabled = false;
                qty6.Text = null;
                qty7.Enabled = false;
                qty7.Text = null;
                qty8.Enabled = false;
                qty8.Text = null;
                qty9.Enabled = false;
                qty9.Text = null;
                qty10.Enabled = false;
                qty10.Text = null;

                price1.Enabled = true;
                price1.Text = null;
                price2.Enabled = false;
                price2.Text = null;
                price3.Enabled = false;
                price3.Text = null;
                price4.Enabled = false;
                price4.Text = null;
                price5.Enabled = false;
                price5.Text = null;
                price6.Enabled = false;
                price6.Text = null;
                price7.Enabled = false;
                price7.Text = null;
                price8.Enabled = false;
                price8.Text = null;
                price9.Enabled = false;
                price9.Text = null;
                price10.Enabled = false;
                price10.Text = null;
            }
        }

        private void buttonCancel_Click(object sender, EventArgs e)
        {
            this.Dispose();
        }

        private void buttonAdd_Click(object sender, EventArgs e)
        {
            string error = null;

            string input_date = null;
            string partno = null;
            string supplierid = null;
            int quantity1 = 0, quantity2 = 0, quantity3 = 0, quantity4 = 0, quantity5 = 0;
            int quantity6 = 0, quantity7 = 0, quantity8 = 0, quantity9 = 0, quantity10 = 0;

            float price1_value = 0, price2_value = 0, price3_value = 0, price4_value = 0, price5_value = 0;
            float price6_value = 0, price7_value = 0, price8_value = 0, price9_value = 0, price10_value = 0;

            string currency = null;
            string note = null;


            ComboboxItem item = new ComboboxItem();

            if (this.inputDate.EditValue == null)
            {
                error = "Please select Input Date";
            }
            else
            {
                input_date = DateTime.Parse(inputDate.EditValue.ToString()).Date.ToString("yyyyMMdd");
            }

            if (string.IsNullOrEmpty(error))
            {
                if (this.partNo.SelectedIndex >= 0)
                {
                    item = (ComboboxItem)this.partNo.SelectedItem;
                    partno = item.Value.ToString();
                }
                else
                {
                    error = "Please select Part No";
                }
            }

            if (string.IsNullOrEmpty(error))
            {
                if (this.supplierID.Properties.Items.Count > 0)
                {
                    if (this.supplierID.SelectedIndex >= 0)
                    {
                        item = (ComboboxItem)this.supplierID.SelectedItem;
                        supplierid = item.Value.ToString();
                    }
                    else
                    {
                        error = "Please select Supplier";
                    }
                }
            }
            
            if (string.IsNullOrEmpty(error))
            {
                if (!this.qtyBasePrice.Checked)
                {
                    quantity1 = 9999999;
                    if (!float.TryParse(this.price1.Text, out price1_value))
                    {
                        error = "Invallid Price";
                    }
                    else
                    {
                        if (price1_value <= 0)
                        {
                            error = "Price value must be greater than 0.";
                        }
                        else
                        {
                            if (checkExist(partno, supplierid, quantity1.ToString()))
                            {
                                error = "Quotation with Quantity 1 exist. Please enter another one.";
                            }
                        }
                    }
                }
                else
                {
                    //Check Quantity 1 and Price 1
                    if (!Int32.TryParse(this.qty1.Text, out quantity1))
                    {
                        error = "Invalid Quantity 1";
                    }
                    else
                    {
                        if (quantity1 <= 0)
                        {
                            error = "Quantity 1 must be greater than 0";
                        }
                    }

                    if (string.IsNullOrEmpty(error))
                    {
                        if (!float.TryParse(this.price1.Text, out price1_value))
                        {
                            error = "Invalid Price 1";
                        }
                        else
                        {
                            if (price1_value <= 0)
                            {
                                error = "Price 1 must be greater than 0";
                            }                            
                        }
                    }
                    
                    if (string.IsNullOrEmpty(error))
                    {
                        if (checkExist(partno, supplierid, quantity1.ToString()))
                        {
                            error = "Quotation with Quantity 1 exist. Please enter another one.";
                        }
                    }

                    //Check Quantity 2 and Price 2
                    if (string.IsNullOrEmpty(error))
                    {
                        if (!string.IsNullOrEmpty(this.qty2.Text))
                        {
                            if (!Int32.TryParse(this.qty2.Text, out quantity2))
                            {
                                error = "Invalid Quantity 2";
                            }
                            else
                            {
                                if (quantity2 < 0)
                                {
                                    error = "Quantity 2 must be greater than 0";
                                }
                            }
                        }
                    }

                    if (string.IsNullOrEmpty(error))
                    {
                        if (!string.IsNullOrEmpty(this.price2.Text))
                        {
                            if (!float.TryParse(this.price2.Text, out price2_value))
                            {
                                error = "Invalid Price 2";
                            }
                        }
                    }

                    if (string.IsNullOrEmpty(error))
                    {
                        if (quantity2 > 0)
                        {
                            if (price2_value <= 0)
                            {
                                error = "Price 2 must be greater than 0";
                            }
                            else
                            {
                                if (quantity2 <= quantity1)
                                {
                                    error = "Quantity 2 must be greater than Quantity 1";
                                }
                                else
                                {
                                    if (price2_value >= price1_value)
                                    {
                                        error = "Price 1 must be greter than Price 2";
                                    }
                                }
                            }
                        }
                    }
                    if (string.IsNullOrEmpty(error))
                    {
                        if (checkExist(partno, supplierid, quantity2.ToString()))
                        {
                            error = "Quotation with Quantity 2 exist. Please enter another one.";
                        }
                    }

                    //Check Quantity 3 and Price 3
                    if (string.IsNullOrEmpty(error))
                    {
                        if (!string.IsNullOrEmpty(this.qty3.Text))
                        {
                            if (!Int32.TryParse(this.qty3.Text, out quantity3))
                            {
                                error = "Invalid Quantity 3";
                            }
                            else
                            {
                                if (quantity3 < 0)
                                {
                                    error = "Quantity 3 must be greater than 0";
                                }
                            }
                        }
                    }

                    if (string.IsNullOrEmpty(error))
                    {
                        if (!string.IsNullOrEmpty(this.price3.Text))
                        {
                            if (!float.TryParse(this.price3.Text, out price3_value))
                            {
                                error = "Invalid Price 3";
                            }
                        }
                    }

                    if (string.IsNullOrEmpty(error))
                    {
                        if (quantity3 > 0)
                        {
                            if (price3_value <= 0)
                            {
                                error = "Price 3 must be greater than 0";
                            }
                            else
                            {
                                if (quantity3 <= quantity2)
                                {
                                    error = "Quantity 3 must be greater than Quantity 2";
                                }
                                else
                                {
                                    if (price3_value >= price2_value)
                                    {
                                        error = "Price 2 must be greter than Price 3";
                                    }
                                }
                            }
                        }
                    }

                    if (string.IsNullOrEmpty(error))
                    {
                        if (checkExist(partno, supplierid, quantity3.ToString()))
                        {
                            error = "Quotation with Quantity 3 exist. Please enter another one.";
                        }
                    }

                    //Check Quantity 4 and Price 4
                    if (string.IsNullOrEmpty(error))
                    {
                        if (!string.IsNullOrEmpty(this.qty4.Text))
                        {
                            if (!Int32.TryParse(this.qty4.Text, out quantity4))
                            {
                                error = "Invalid Quantity 4";
                            }
                            else
                            {
                                if (quantity4 < 0)
                                {
                                    error = "Quantity 4 must be greater than 0";
                                }
                            }
                        }
                    }

                    if (string.IsNullOrEmpty(error))
                    {
                        if (!string.IsNullOrEmpty(this.price4.Text))
                        {
                            if (!float.TryParse(this.price4.Text, out price4_value))
                            {
                                error = "Invalid Price 4";
                            }
                        }
                    }

                    if (string.IsNullOrEmpty(error))
                    {
                        if (quantity4 > 0)
                        {
                            if (price4_value <= 0)
                            {
                                error = "Price 4 must be greater than 0";
                            }
                            else
                            {
                                if (quantity4 <= quantity3)
                                {
                                    error = "Quantity 4 must be greater than Quantity 3";
                                }
                                else
                                {
                                    if (price4_value >= price3_value)
                                    {
                                        error = "Price 3 must be greter than Price 4";
                                    }
                                }
                            }
                        }
                    }

                    if (string.IsNullOrEmpty(error))
                    {
                        if (checkExist(partno, supplierid, quantity4.ToString()))
                        {
                            error = "Quotation with Quantity 4 exist. Please enter another one.";
                        }
                    }

                    //Check Quantity 5 and Price 5
                    if (string.IsNullOrEmpty(error))
                    {
                        if (!string.IsNullOrEmpty(this.qty5.Text))
                        {
                            if (!Int32.TryParse(this.qty5.Text, out quantity5))
                            {
                                error = "Invalid Quantity 5";
                            }
                            else
                            {
                                if (quantity5 < 0)
                                {
                                    error = "Quantity 5 must be greater than 0";
                                }
                            }
                        }
                    }

                    if (string.IsNullOrEmpty(error))
                    {
                        if (!string.IsNullOrEmpty(this.price5.Text))
                        {
                            if (!float.TryParse(this.price5.Text, out price5_value))
                            {
                                error = "Invalid Price 5";
                            }
                        }
                    }

                    if (string.IsNullOrEmpty(error))
                    {
                        if (quantity5 > 0)
                        {
                            if (price5_value <= 0)
                            {
                                error = "Price 5 must be greater than 0";
                            }
                            else
                            {
                                if (quantity5 <= quantity4)
                                {
                                    error = "Quantity 5 must be greater than Quantity 4";
                                }
                                else
                                {
                                    if (price5_value >= price4_value)
                                    {
                                        error = "Price 4 must be greter than Price 5";
                                    }
                                }
                            }
                        }
                    }

                    if (string.IsNullOrEmpty(error))
                    {
                        if (checkExist(partno, supplierid, quantity5.ToString()))
                        {
                            error = "Quotation with Quantity 5 exist. Please enter another one.";
                        }
                    }

                    //Check Quantity 6 and Price 6
                    if (string.IsNullOrEmpty(error))
                    {
                        if (!string.IsNullOrEmpty(this.qty6.Text))
                        {
                            if (!Int32.TryParse(this.qty6.Text, out quantity6))
                            {
                                error = "Invalid Quantity 6";
                            }
                            else
                            {
                                if (quantity6 < 0)
                                {
                                    error = "Quantity 6 must be greater than 0";
                                }
                            }
                        }
                    }

                    if (string.IsNullOrEmpty(error))
                    {
                        if (!string.IsNullOrEmpty(this.price6.Text))
                        {
                            if (!float.TryParse(this.price6.Text, out price6_value))
                            {
                                error = "Invalid Price 6";
                            }
                        }
                    }

                    if (string.IsNullOrEmpty(error))
                    {
                        if (quantity6 > 0)
                        {
                            if (price6_value <= 0)
                            {
                                error = "Price 6 must be greater than 0";
                            }
                            else
                            {
                                if (quantity6 <= quantity5)
                                {
                                    error = "Quantity 6 must be greater than Quantity 5";
                                }
                                else
                                {
                                    if (price6_value >= price5_value)
                                    {
                                        error = "Price 5 must be greter than Price 6";
                                    }
                                }
                            }
                        }
                    }

                    if (string.IsNullOrEmpty(error))
                    {
                        if (checkExist(partno, supplierid, quantity6.ToString()))
                        {
                            error = "Quotation with Quantity 6 exist. Please enter another one.";
                        }
                    }

                    //Check Quantity 7 and Price 7
                    if (string.IsNullOrEmpty(error))
                    {
                        if (!string.IsNullOrEmpty(this.qty7.Text))
                        {
                            if (!Int32.TryParse(this.qty7.Text, out quantity7))
                            {
                                error = "Invalid Quantity 7";
                            }
                            else
                            {
                                if (quantity7 < 0)
                                {
                                    error = "Quantity 7 must be greater than 0";
                                }
                            }
                        }
                    }

                    if (string.IsNullOrEmpty(error))
                    {
                        if (!string.IsNullOrEmpty(this.price7.Text))
                        {
                            if (!float.TryParse(this.price7.Text, out price7_value))
                            {
                                error = "Invalid Price 7";
                            }
                        }
                    }

                    if (string.IsNullOrEmpty(error))
                    {
                        if (quantity7 > 0)
                        {
                            if (price7_value <= 0)
                            {
                                error = "Price 7 must be greater than 0";
                            }
                            else
                            {
                                if (quantity7 <= quantity6)
                                {
                                    error = "Quantity 7 must be greater than Quantity 6";
                                }
                                else
                                {
                                    if (price7_value >= price6_value)
                                    {
                                        error = "Price 6 must be greter than Price 7";
                                    }
                                }
                            }
                        }
                    }

                    if (string.IsNullOrEmpty(error))
                    {
                        if (checkExist(partno, supplierid, quantity7.ToString()))
                        {
                            error = "Quotation with Quantity 7 exist. Please enter another one.";
                        }
                    }

                    //Check Quantity 8 and Price 8
                    if (string.IsNullOrEmpty(error))
                    {
                        if (!string.IsNullOrEmpty(this.qty8.Text))
                        {
                            if (!Int32.TryParse(this.qty8.Text, out quantity8))
                            {
                                error = "Invalid Quantity 8";
                            }
                            else
                            {
                                if (quantity8 < 0)
                                {
                                    error = "Quantity 8 must be greater than 0";
                                }
                            }
                        }
                    }

                    if (string.IsNullOrEmpty(error))
                    {
                        if (!string.IsNullOrEmpty(this.price8.Text))
                        {
                            if (!float.TryParse(this.price8.Text, out price8_value))
                            {
                                error = "Invalid Price 8";
                            }
                        }
                    }

                    if (string.IsNullOrEmpty(error))
                    {
                        if (quantity8 > 0)
                        {
                            if (price8_value <= 0)
                            {
                                error = "Price 8 must be greater than 0";
                            }
                            else
                            {
                                if (quantity8 <= quantity7)
                                {
                                    error = "Quantity 8 must be greater than Quantity 7";
                                }
                                else
                                {
                                    if (price8_value >= price7_value)
                                    {
                                        error = "Price 7 must be greter than Price 8";
                                    }
                                }
                            }
                        }
                    }

                    if (string.IsNullOrEmpty(error))
                    {
                        if (checkExist(partno, supplierid, quantity8.ToString()))
                        {
                            error = "Quotation with Quantity 8 exist. Please enter another one.";
                        }
                    }

                    //Check Quantity 9 and Price 9
                    if (string.IsNullOrEmpty(error))
                    {
                        if (!string.IsNullOrEmpty(this.qty9.Text))
                        {
                            if (!Int32.TryParse(this.qty9.Text, out quantity9))
                            {
                                error = "Invalid Quantity 9";
                            }
                            else
                            {
                                if (quantity9 < 0)
                                {
                                    error = "Quantity 9 must be greater than 0";
                                }
                            }
                        }
                    }

                    if (string.IsNullOrEmpty(error))
                    {
                        if (!string.IsNullOrEmpty(this.price9.Text))
                        {
                            if (!float.TryParse(this.price9.Text, out price9_value))
                            {
                                error = "Invalid Price 9";
                            }
                        }
                    }

                    if (string.IsNullOrEmpty(error))
                    {
                        if (quantity9 > 0)
                        {
                            if (price9_value <= 0)
                            {
                                error = "Price 9 must be greater than 0";
                            }
                            else
                            {
                                if (quantity9 <= quantity8)
                                {
                                    error = "Quantity 9 must be greater than Quantity 8";
                                }
                                else
                                {
                                    if (price9_value >= price8_value)
                                    {
                                        error = "Price 8 must be greter than Price 9";
                                    }
                                }
                            }
                        }
                    }

                    if (string.IsNullOrEmpty(error))
                    {
                        if (checkExist(partno, supplierid, quantity9.ToString()))
                        {
                            error = "Quotation with Quantity 9 exist. Please enter another one.";
                        }
                    }

                    //Check Quantity 10 and Price 10
                    if (string.IsNullOrEmpty(error))
                    {
                        if (!string.IsNullOrEmpty(this.qty10.Text))
                        {
                            if (!Int32.TryParse(this.qty10.Text, out quantity10))
                            {
                                error = "Invalid Quantity 10";
                            }
                            else
                            {
                                if (quantity10 < 0)
                                {
                                    error = "Quantity 10 must be greater than 0";
                                }
                            }
                        }
                    }

                    if (string.IsNullOrEmpty(error))
                    {
                        if (!string.IsNullOrEmpty(this.price10.Text))
                        {
                            if (!float.TryParse(this.price10.Text, out price10_value))
                            {
                                error = "Invalid Price 10";
                            }
                        }
                    }

                    if (string.IsNullOrEmpty(error))
                    {
                        if (quantity10 > 0)
                        {
                            if (price10_value <= 0)
                            {
                                error = "Price 10 must be greater than 0";
                            }
                            else
                            {
                                if (quantity10 <= quantity9)
                                {
                                    error = "Quantity 10 must be greater than Quantity 9";
                                }
                                else
                                {
                                    if (price10_value >= price9_value)
                                    {
                                        error = "Price 9 must be greter than Price 10";
                                    }
                                }
                            }
                        }
                    }

                    if (string.IsNullOrEmpty(error))
                    {
                        if (checkExist(partno, supplierid, quantity1.ToString()))
                        {
                            error = "Quotation with Quantity 1 exist. Please enter another one.";
                        }
                    }
                }
            }

            if (string.IsNullOrEmpty(error))
            {
                if (this.currencyID.SelectedIndex < 0)
                {
                    error = "Please select Currency";
                }
                else
                {
                    item = (ComboboxItem)this.currencyID.SelectedItem;
                    currency = item.Value.ToString();
                }
            }            

            if (string.IsNullOrEmpty(error))
            {
                note = this.noteValue.Text;                
                try
                {
                    myConnection.Open();
                    SqlCommand cmd_sql = myConnection.CreateCommand();
                    if ((quantity1 > 0) && (price1_value > 0))
                    {
                        cmd_sql.CommandText = "INSERT INTO [241_ImportQuotation] (Date, PartNo, SupplierID,"
                            + " Quantity, Price, CurrencyID, Note)"
                            + " VALUES ('" + input_date + "', '" + partno + "', '" + supplierid + "', " + quantity1 + ","
                            + " " + price1_value + ", '" + currency + "', '" + note + "')";
                        cmd_sql.ExecuteNonQuery();
                    }
                    if ((quantity2 > 0) && (price2_value > 0))
                    {
                        cmd_sql.CommandText = "INSERT INTO [241_ImportQuotation] (Date, PartNo, SupplierID,"
                            + " Quantity, Price, CurrencyID, Note)"
                            + " VALUES ('" + input_date + "', '" + partno + "', '" + supplierid + "', " + quantity2 + ","
                            + " " + price2_value + ", '" + currency + "', '" + note + "')";
                        cmd_sql.ExecuteNonQuery();
                    }
                    if ((quantity3 > 0) && (price3_value > 0))
                    {
                        cmd_sql.CommandText = "INSERT INTO [241_ImportQuotation] (Date, PartNo, SupplierID,"
                            + " Quantity, Price, CurrencyID, Note)"
                            + " VALUES ('" + input_date + "', '" + partno + "', '" + supplierid + "', " + quantity3 + ","
                            + " " + price3_value + ", '" + currency + "', '" + note + "')";
                        cmd_sql.ExecuteNonQuery();
                    }
                    if ((quantity4 > 0) && (price4_value > 0))
                    {
                        cmd_sql.CommandText = "INSERT INTO [241_ImportQuotation] (Date, PartNo, SupplierID,"
                            + " Quantity, Price, CurrencyID, Note)"
                            + " VALUES ('" + input_date + "', '" + partno + "', '" + supplierid + "', " + quantity4 + ","
                            + " " + price4_value + ", '" + currency + "', '" + note + "')";
                        cmd_sql.ExecuteNonQuery();
                    }
                    if ((quantity5 > 0) && (price5_value > 0))
                    {
                        cmd_sql.CommandText = "INSERT INTO [241_ImportQuotation] (Date, PartNo, SupplierID,"
                            + " Quantity, Price, CurrencyID, Note)"
                            + " VALUES ('" + input_date + "', '" + partno + "', '" + supplierid + "', " + quantity5 + ","
                            + " " + price5_value + ", '" + currency + "', '" + note + "')";
                        cmd_sql.ExecuteNonQuery();
                    }
                    if ((quantity6 > 0) && (price6_value > 0))
                    {
                        cmd_sql.CommandText = "INSERT INTO [241_ImportQuotation] (Date, PartNo, SupplierID,"
                            + " Quantity, Price, CurrencyID, Note)"
                            + " VALUES ('" + input_date + "', '" + partno + "', '" + supplierid + "', " + quantity6 + ","
                            + " " + price6_value + ", '" + currency + "', '" + note + "')";
                        cmd_sql.ExecuteNonQuery();
                    }
                    if ((quantity7 > 0) && (price7_value > 0))
                    {
                        cmd_sql.CommandText = "INSERT INTO [241_ImportQuotation] (Date, PartNo, SupplierID,"
                            + " Quantity, Price, CurrencyID, Note)"
                            + " VALUES ('" + input_date + "', '" + partno + "', '" + supplierid + "', " + quantity7 + ","
                            + " " + price7_value + ", '" + currency + "', '" + note + "')";
                        cmd_sql.ExecuteNonQuery();
                    }
                    if ((quantity8 > 0) && (price8_value > 0))
                    {
                        cmd_sql.CommandText = "INSERT INTO [241_ImportQuotation] (Date, PartNo, SupplierID,"
                            + " Quantity, Price, CurrencyID, Note)"
                            + " VALUES ('" + input_date + "', '" + partno + "', '" + supplierid + "', " + quantity8 + ","
                            + " " + price8_value + ", '" + currency + "', '" + note + "')";
                        cmd_sql.ExecuteNonQuery();
                    }
                    if ((quantity9 > 0) && (price9_value > 0))
                    {
                        cmd_sql.CommandText = "INSERT INTO [241_ImportQuotation] (Date, PartNo, SupplierID,"
                            + " Quantity, Price, CurrencyID, Note)"
                            + " VALUES ('" + input_date + "', '" + partno + "', '" + supplierid + "', " + quantity9 + ","
                            + " " + price9_value + ", '" + currency + "', '" + note + "')";
                        cmd_sql.ExecuteNonQuery();
                    }
                    if ((quantity10 > 0) && (price10_value > 0))
                    {
                        cmd_sql.CommandText = "INSERT INTO [241_ImportQuotation] (Date, PartNo, SupplierID,"
                            + " Quantity, Price, CurrencyID, Note)"
                            + " VALUES ('" + input_date + "', '" + partno + "', '" + supplierid + "', " + quantity10 + ","
                            + " " + price10_value + ", '" + currency + "', '" + note + "')";
                        cmd_sql.ExecuteNonQuery();
                    }
                    cmd_sql.Dispose();
                    myConnection.Close();

                    MessageBox.Show("Update successfully!");
                    
                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show(ex.Message);
                    //MessageBox.Show("Cannot update database");
                }
            }
            else
            {
                MessageBox.Show(error);
            }
        }

        private bool checkExist(string part, string supplier, string quantity)
        {
            bool ret = false;

            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT COUNT(*) FROM [241_ImportQuotation]"
                    + " WHERE PartNo='" + part + "' AND SupplierID='" + supplier + "'"
                    + " AND Quantity=" + quantity + " AND [Old] = 0";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                if (dataReader.Read())
                {
                    if (Int32.Parse(dataReader[0].ToString()) > 0)
                    {
                        ret = true;
                    }
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                ret = false;
            }
            return ret;
        }
    }
}
